<!DOCTYPE html>
<html lang="en">
<head>
		<title>Snippets &mdash; Articles tagged "sqlite"</title>
		<meta charset="utf-8" />
		<link rel="profile" href="http://gmpg.org/xfn/11" />
		<link rel="stylesheet" type="text/css" href="https://corteousquestions.club/theme/css/style.css" />
		<link rel='stylesheet' id='oswald-css'  href='http://fonts.googleapis.com/css?family=Oswald&#038;ver=3.3.2' type='text/css' media='all' />
		<style type="text/css">
			body.custom-background { background-color: #f5f5f5; }
		</style>
		<link rel="alternate" type="application/atom+xml"
			title="Snippets — Flux Atom"
			href="https://corteousquestions.club/" /> 
		<!--[if lte IE 8]><script src="https://corteousquestions.club/theme/js/html5shiv.js"></script><![endif]-->
</head>

<body class="home blog custom-background " >
	<div id="container">
		<div id="header">
				<h1 id="site-title"><a href="https://corteousquestions.club">Snippets</a></h1>
<h2 id="site-description">All that I will forget is here</h2>		</div><!-- /#banner -->
		
		<div id="menu">
			<div class="menu-navigation-container">
				<ul id="menu-navigation" class="menu">
						<li class="menu-item menu-item-type-post_type menu-item-object-page"><a href="https://corteousquestions.club/category/misc.html">misc</a></li>

				</ul>
			</div> <!--/#menu-navigation-container-->
		</div><!-- /#menu -->
		
		<div class="page-title">
<div class="page-title">
	<h2>Tagged with <span>"sqlite"</span> &hellip;</h2>
</div>
		</div>
	
		<div id="contents">
<div class="post type-post status-publish format-standard hentry category-general" id="post">
	<div class="entry-meta">
		<div class="date"><a href="https://corteousquestions.club/list-triggers-in-sqlite-db.html">Sat 17 April 2021</a></div>
		<span class="byline">By <a href="https://corteousquestions.club/author/mclds.html">mclds</a></span>
			<span class="cat-links"><a href="https://corteousquestions.club/category/misc.html" title="View all posts in misc" rel="category tag">misc</a></span>
	</div> <!-- /#entry-meta -->
	<div class="main">
		<h2 class="entry-title">
			<a href="https://corteousquestions.club/list-triggers-in-sqlite-db.html" title="Permalink to "List triggers in SQLite db"" rel="bookmark">"List triggers in SQLite db"</a>
		</h2>
		<div class="entry-content">
			<p>select * from sqlite_master where type = 'trigger';</p>
		</div> <!--/#entry-content-->
    		<span class="tag-links"><strong>Tagged</strong>
 <a href="https://corteousquestions.club/tag/search.html" rel="tag">"search"]</a>,  <a href="https://corteousquestions.club/tag/sqlite.html" rel="tag">"sqlite"</a>,  <a href="https://corteousquestions.club/tag/triggers.html" rel="tag">["triggers"</a>    		</span>
	</div> <!--/#main-->
</div>  <!--/#post--><div class="post type-post status-publish format-standard hentry category-general" id="post">
	<div class="entry-meta">
		<div class="date"><a href="https://corteousquestions.club/create-virtual-table-sqlite.html">Sun 21 April 2019</a></div>
		<span class="byline">By <a href="https://corteousquestions.club/author/mclds.html">mclds</a></span>
			<span class="cat-links"><a href="https://corteousquestions.club/category/misc.html" title="View all posts in misc" rel="category tag">misc</a></span>
	</div> <!-- /#entry-meta -->
	<div class="main">
		<h2 class="entry-title">
			<a href="https://corteousquestions.club/create-virtual-table-sqlite.html" title="Permalink to Create SQLite virtual table" rel="bookmark">Create SQLite virtual table</a>
		</h2>
		<div class="entry-content">
			<p>Example:</p>
<hr>
<p>CREATE VIRTUAL TABLE BookSearch USING fts5(ID, Title, Desc);
 INSERT INTO BookSearch SELECT ID, Title, Desc FROM Book;</p>
<hr>
<p>Search like this:</p>
<hr>
<p>SELECT Title FROM BookSearch WHERE BookSearch MATCH 'cat';</p>
<hr>
		</div> <!--/#entry-content-->
    		<span class="tag-links"><strong>Tagged</strong>
 <a href="https://corteousquestions.club/tag/fts5.html" rel="tag">fts5</a>,  <a href="https://corteousquestions.club/tag/sqlite.html" rel="tag">sqlite</a>,  <a href="https://corteousquestions.club/tag/virtual.html" rel="tag">virtual</a>    		</span>
	</div> <!--/#main-->
</div>  <!--/#post--><div class="post type-post status-publish format-standard hentry category-general" id="post">
	<div class="entry-meta">
		<div class="date"><a href="https://corteousquestions.club/alter-sqlite-table.html">Sun 21 April 2019</a></div>
		<span class="byline">By <a href="https://corteousquestions.club/author/mclds.html">mclds</a></span>
			<span class="cat-links"><a href="https://corteousquestions.club/category/misc.html" title="View all posts in misc" rel="category tag">misc</a></span>
	</div> <!-- /#entry-meta -->
	<div class="main">
		<h2 class="entry-title">
			<a href="https://corteousquestions.club/alter-sqlite-table.html" title="Permalink to Alter SQLite tables" rel="bookmark">Alter SQLite tables</a>
		</h2>
		<div class="entry-content">
			<p>Para alterar uma table que não possa ser alterada por
 sqlite-utils,podemos usar este modelo. É também mais seguro,
 que o sqlite-utils também faz alguma merda.</p>
<hr>
<p>PRAGMA foreign_keys=off;
 BEGIN TRANSACTION;
 CREATE TABLE IF NOT EXISTS new_table(
    column_definition,
 );
 INSERT INTO new_table(column_list)
 SELECT column_list
 FROM table;
 DROP TABLE table;
 ALTER TABLE new_table RENAME TO table;
 COMMIT;
 PRAGMA foreign_keys=on;</p>
<hr>
		</div> <!--/#entry-content-->
    		<span class="tag-links"><strong>Tagged</strong>
 <a href="https://corteousquestions.club/tag/sqlite.html" rel="tag">sqlite</a>,  <a href="https://corteousquestions.club/tag/table.html" rel="tag">table</a>,  <a href="https://corteousquestions.club/tag/talbes.html" rel="tag">talbes</a>    		</span>
	</div> <!--/#main-->
</div>  <!--/#post--><div class="post type-post status-publish format-standard hentry category-general" id="post">
	<div class="entry-meta">
		<div class="date"><a href="https://corteousquestions.club/create-sqlite-tables.html">Sun 21 April 2019</a></div>
		<span class="byline">By <a href="https://corteousquestions.club/author/mclds.html">mclds</a></span>
			<span class="cat-links"><a href="https://corteousquestions.club/category/misc.html" title="View all posts in misc" rel="category tag">misc</a></span>
	</div> <!-- /#entry-meta -->
	<div class="main">
		<h2 class="entry-title">
			<a href="https://corteousquestions.club/create-sqlite-tables.html" title="Permalink to Create SQLite table" rel="bookmark">Create SQLite table</a>
		</h2>
		<div class="entry-content">
			<p>Example:</p>
<hr>
<p>CREATE TABLE notes (
 id INTEGER PRIMARY KEY,
 title TEXT,
 k1 TEXT,
 k2 TEXT,
 k3 TEXT,
 note TEXT,
 time DATETIME DEFAULT CURRENT_TIMESTAMP);</p>
<hr>
		</div> <!--/#entry-content-->
    		<span class="tag-links"><strong>Tagged</strong>
 <a href="https://corteousquestions.club/tag/sqlite.html" rel="tag">sqlite</a>,  <a href="https://corteousquestions.club/tag/table.html" rel="tag">table</a>,  <a href="https://corteousquestions.club/tag/tables.html" rel="tag">tables</a>    		</span>
	</div> <!--/#main-->
</div>  <!--/#post--><div class="post type-post status-publish format-standard hentry category-general" id="post">
	<div class="entry-meta">
		<div class="date"><a href="https://corteousquestions.club/triggers-for-sqlite3.html">Sun 21 April 2019</a></div>
		<span class="byline">By <a href="https://corteousquestions.club/author/mclds.html">mclds</a></span>
			<span class="cat-links"><a href="https://corteousquestions.club/category/misc.html" title="View all posts in misc" rel="category tag">misc</a></span>
	</div> <!-- /#entry-meta -->
	<div class="main">
		<h2 class="entry-title">
			<a href="https://corteousquestions.club/triggers-for-sqlite3.html" title="Permalink to Create triggers for Sqlite3 databases" rel="bookmark">Create triggers for Sqlite3 databases</a>
		</h2>
		<div class="entry-content">
			<p>Here's one example of the three trigger you must create:</p>
<hr>
<p>CREATE TRIGGER aft_insert AFTER INSERT ON pwd
 BEGIN
 INSERT INTO pwd_fts(pwdid, site, username, passwd, comment, time)
 VALUES(new.pwdid, new.site, new.username, new.passwd, new.comment, new.time);
 END;</p>
<hr>
<p>CREATE TRIGGER aft_del AFTER DELETE ON pwd
 BEGIN
INSERT INTO pwd_fts(pwdid, site, username, passwd, comment, time)
 VALUES ('delete', old.pwdid, old.site, old.username, old.passwd, old.comment, old.time);
 END;</p>
<hr>
<p>CREATE TRIGGER aft_updt AFTER UPDATE ON pwd
 BEGIN
 INSERT INTO pwd_fts(pwdid, site, username, passwd, comment, time)
 VALUES ('delete', old.pwdid, old.site, old.username, old.passwd, old.comment, old.time);
 INSERT INTO pwd_fts(pwdid, site, username, passwd, comment, time)
 VALUES(new.pwdid, new.site, new.username, new.passwd, new.comment, new.time);
 END;</p>
<hr>
		</div> <!--/#entry-content-->
    		<span class="tag-links"><strong>Tagged</strong>
 <a href="https://corteousquestions.club/tag/sqlite.html" rel="tag">sqlite</a>,  <a href="https://corteousquestions.club/tag/trigger-triggers.html" rel="tag">trigger. triggers</a>    		</span>
	</div> <!--/#main-->
</div>  <!--/#post-->		</div>
		
		<div id="footer">
			<p>Powered by <a href="http://getpelican.com">Pelican</a>, theme by <a href="http://bunnyman.info">tBunnyMan</a>.</p>
		</div><!-- /#footer -->
	</div><!-- /#container -->
	<div style="display:none"></div>
</body>
</html>